package nc.jdbc.framework;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import nc.bs.extable.cfg.EXTableMgrFactory;
import nc.bs.extable.dao.DAOUtil;
import nc.bs.extable.dao.EXTableParam;
import nc.bs.extable.util.EXTableInnerTool;
import nc.bs.logging.Logger;
import nc.jdbc.framework.exception.DbException;
import nc.jdbc.framework.exception.ExceptionFactory;
import nc.jdbc.framework.exception.UnKnownException;
import nc.jdbc.framework.generator.SequenceGenerator;
import nc.jdbc.framework.mapping.AttributeMapping;
import nc.jdbc.framework.mapping.BeanMapping;
import nc.jdbc.framework.mapping.IMappingMeta;
import nc.jdbc.framework.mapping.MappingMetaManager;
import nc.jdbc.framework.processor.BaseProcessor;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.BeanMappingListProcessor;
import nc.jdbc.framework.processor.ResultSetProcessor;
import nc.jdbc.framework.util.DBUtil;
import nc.jdbc.framework.util.SQLHelper;
import nc.vo.jcom.lang.StringUtil;
import nc.vo.pub.BeanHelper;
import nc.vo.pub.SuperVO;
import uap.sec.esapi.UAPESAPI;













public class JdbcPersistenceManager
  extends PersistenceManager
{
  JdbcSession session;
  String dataSource;
  private DatabaseMetaData dbmd;
  private static Map<String, ColCache> colCacheMap = new ConcurrentHashMap();
  
  class ColCache {
    private Map<String, Map<String, Integer>> typeCache = new ConcurrentHashMap();
    private Map<String, Map<String, Integer>> sizeCache = new ConcurrentHashMap();
  }



  
  protected JdbcPersistenceManager() throws DbException {
    this.dataSource = null;
    this.dbmd = null;
    init();
  }




  
  protected JdbcPersistenceManager(String dataSource) throws DbException {
    this.dataSource = null;
    this.dbmd = null;
    this.dataSource = dataSource;
    init();
  } protected JdbcPersistenceManager(JdbcSession session) {
    this.dataSource = null;
    this.dbmd = null;
    session.setMaxRows(this.maxRows);
    this.session = session;
  }






  
  public JdbcSession getJdbcSession() { return this.session; }




  
  
  public void release() {
	    if (this.dbmd != null)
	      this.dbmd = null; 
	    if (this.session != null) {
	      this.session.closeAll();
	      this.session = null;
	    } 
	  }






  
  public String insertWithPK(SuperVO vo) throws DbException {
    String[] pk = insertWithPK(new SuperVO[] { vo });
    return pk[0];
  }






  
  public String insert(SuperVO vo) throws DbException {
    String[] pk = insert(new SuperVO[] { vo });
    return pk[0];
  }







  
  public String[] insertWithPK(List vos) throws DbException { return insertWithPK((SuperVO[])vos.toArray(new SuperVO[0])); }









  
  public String[] insert(List vos) throws DbException { return insert((SuperVO[])vos.toArray(new SuperVO[0])); }







  
  public String[] insertWithPK(SuperVO[] vos) throws DbException { return insert(vos, true); }









  
  public String[] insert(SuperVO[] vos) throws DbException { return insert(vos, false); }








  
  private String[] preparePK(SuperVO[] vos, boolean withPK) {
    String corpPk = null;
    if (EXTableMgrFactory.getInstance().isEXTable(vos[0].getTableName())) {
      corpPk = EXTableInnerTool.getPKTagByColumnValue(vos[0]);
    } else {
      corpPk = SQLHelper.getCorpPk();
    } 
    if (withPK) {
      String[] pks = new String[vos.length];
      int[] idx = new int[vos.length];
      int length = 0;
      for (int i = 0; i < vos.length; i++) {
        if (vos[i] != null) {

          
          String thePK = vos[i].getPrimaryKey();
          if (thePK == null || thePK.trim().length() == 0) {
            idx[length++] = i;
          } else {
            pks[i] = thePK;
          } 
        } 
      } 
      
      if (length > 0) {
        String[] npks = (new SequenceGenerator(this.dataSource)).generate(corpPk, length);
        for (int i = 0; i < length; i++) {
          vos[idx[i]].setPrimaryKey(npks[i]);
          pks[idx[i]] = npks[i];
        } 
      } 
      return pks;
    } 
    
    String[] pks = (new SequenceGenerator(this.dataSource)).generate(corpPk, vos.length);
    for (int i = 0; i < vos.length; i++) {
      if (vos[i] != null) {
        vos[i].setPrimaryKey(pks[i]);
      } else {
        pks[i] = null;
      } 
    } 
    return pks;
  }


  
  protected String[] insert(SuperVO[] vos, boolean withPK) throws DbException {
    isNull(vos);
    if (vos.length == 0) {
      return new String[0];
    }
    String[] pks = null;
    String tableName = getTableName(vos[0]);
    
    Map<String, Integer> types = getColmnTypes(tableName);
    Map<String, Integer> sizes = getColmnSize(tableName);
    
    String[] names = getValidNames(vos[0], types);
    
    String sql = SQLHelper.getInsertSQL(tableName, names);
    
    pks = preparePK(vos, withPK);

    
    if (this.session.getDbType() == 12) {
      this.session.setAddTimeStamp(true);
      Logger.debug("JdbcPersistenceManager:true");
    } else {
      this.session.setAddTimeStamp(false);
      Logger.debug("JdbcPersistenceManager:false");
    } 

    
    if (this.session.getDbType() == 0) {
      this.session.setAddTimeStamp(true);
    }
    
    if (vos.length == 1) {
      SQLParameter parameter = getSQLParam(vos[0], names, types, sizes);
      this.session.executeUpdate(sql, parameter);
    } else {
      SQLParameter[] parameters = new SQLParameter[vos.length];
      for (int i = 0; i < vos.length; i++) {
        if (vos[i] != null)
        {
          
          parameters[i] = getSQLParam(vos[i], names, types, sizes);
        }
      } 
      this.session.addBatch(sql, parameters);
      this.session.executeBatch();
    } 
    
    return pks;
  }

  
  public String getTableName(SuperVO superVO) throws DbException {
    String table = superVO.getExTable();
    if (StringUtil.isEmptyWithTrim(table)) {
      table = superVO.getTableName();
    }
    return table;
  }







  
  public String insertObjectWithPK(Object vo, IMappingMeta meta) throws DbException { return insertObjectWithPK(new Object[] { vo }, meta)[0]; }








  
  public String[] insertObjectWithPK(Object[] vo, IMappingMeta meta) throws DbException { return insertObject(vo, meta, true); }


  
  public String insertObject(Object vo, IMappingMeta meta) throws DbException { return insertObject(new Object[] { vo }, meta)[0]; }








  
  public String[] insertObject(Object[] vo, IMappingMeta meta) throws DbException { return insertObject(vo, meta, false); }

  
  protected String[] insertObject(Object[] vo, IMappingMeta meta, boolean withPK) throws DbException {
    isNull(vo);
    if (vo.length == 0) {
      return new String[0];
    }
    
    if (vo[0] instanceof SuperVO) {
      SuperVO[] svos = new SuperVO[vo.length];
      System.arraycopy(vo, 0, svos, 0, vo.length);
      return insert(svos, meta, withPK);
    } 
    
    AttributeMapping map = MappingMetaManager.getMapingMeta(meta);
    
    String tableName = meta.getTableName();
    
    String corpPk = SQLHelper.getCorpPk();
    String[] pk = (new SequenceGenerator(this.dataSource)).generate(corpPk, vo.length);
    Map<String, Integer> types = getColmnTypes(tableName);
    Map<String, Integer> sizes = getColmnSize(tableName);


    
    for (int i = 0; i < vo.length; i++) {
      if (vo[i] != null) {

        
        String beanPkName = map.getAttributeName(meta.getPrimaryKey()).toLowerCase();
        if (withPK) {
          String thePK = (String)BeanHelper.getProperty(vo[i], beanPkName);
          
          if (thePK == null || thePK.trim().length() == 0) {
            BeanHelper.setProperty(vo[i], beanPkName, pk[i]);
          } else {
            pk[i] = thePK;
          } 
        } else {
          BeanHelper.setProperty(vo[i], beanPkName, pk[i]);
        } 
        BeanMapping mapping = new BeanMapping(vo[i], meta);
        if (types != null) {
          mapping.setType(types);
        }
        if (sizes != null) {
          mapping.setSizes(sizes);
        }
        SQLParameter parameter = mapping.getInsertParamter();
        this.session.addBatch(mapping.getInsertSQL(), parameter);
      } 
    }  this.session.executeBatch();
    return pk;
  }






  
  public int update(SuperVO vo) throws DbException {
    if (vo == null) {
      throw new IllegalArgumentException("vo parameter is null");
    }
    return update(new SuperVO[] { vo }, null);
  }

  
  public int update(List vos) throws DbException { return update((SuperVO[])vos.toArray(new SuperVO[0]), null); }



  
  public int update(SuperVO[] vo) throws DbException { return update(vo, null); }



  
  public int update(SuperVO[] vo, String[] fieldNames) throws DbException { return update(vo, fieldNames, null, null); }


  
  public int update(SuperVO[] vo, String[] fieldNames, String whereClause, SQLParameter param) throws DbException {
    String[] names;
    isNull(vo);
    if (vo.length == 0) {
      return 0;
    }
    int row = 0;


    
    String tableName = getTableName(vo[0]);
    String pkName = vo[0].getPKFieldName();


    
    Map<String, Integer> types = getColmnTypes(tableName);
    Map<String, Integer> sizes = getColmnSize(tableName);
    if (fieldNames != null) {
      names = fieldNames;
    } else {
      
      names = getUpdateValidNames(vo[0], types, pkName);
    } 
    
    String sql = SQLHelper.getUpdateSQL(tableName, names, pkName);
    if (vo.length == 1) {
      SQLParameter parameter = getSQLParam(vo[0], names, types, sizes);
      parameter.addParam(vo[0].getPrimaryKey());
      if (whereClause == null) {
        row = this.session.executeUpdate(sql, parameter);
      } else {
        addParameter(parameter, param);
        row = this.session.executeUpdate(sql + " and " + whereClause, parameter);
      } 
    } else {
      for (int i = 0; i < vo.length; i++) {
        if (vo[i] != null) {


          
          SQLParameter parameter = getSQLParam(vo[i], names, types, sizes);
          parameter.addParam(vo[i].getPrimaryKey());
          if (whereClause == null) {
            this.session.addBatch(sql, parameter);
          } else {
            addParameter(parameter, param);
            this.session.addBatch(sql + " and " + whereClause, parameter);
          } 
        } 
      }  row = this.session.executeBatch();
    } 
    return row;
  }

  
  public int updateObject(Object vo, IMappingMeta meta) throws DbException { return updateObject(new Object[] { vo }, meta); }








  
  public int updateObject(Object vo, IMappingMeta meta, String whereClause) throws DbException { return updateObject(new Object[] { vo }, meta, whereClause); }








  
  public int updateObject(Object[] vo, IMappingMeta meta, String whereClause) throws DbException { return updateObject(vo, meta, whereClause, null); }









  
  public int updateObject(Object[] vo, IMappingMeta meta, String whereClause, SQLParameter param) throws DbException {
    isNull(vo);
    if (vo.length == 0) {
      return 0;
    }

    
    if (vo[0] instanceof SuperVO) {
      SuperVO[] svos = new SuperVO[vo.length];
      System.arraycopy(vo, 0, svos, 0, vo.length);
      return update(svos, meta, whereClause, param);
    } 
    
    String tableName = meta.getTableName();
    Map<String, Integer> types = getColmnTypes(tableName);
    Map<String, Integer> sizes = getColmnSize(tableName);
    if (vo.length == 1) {
      if (vo[0] == null)
          return -1; 
      BeanMapping mapping = new BeanMapping(vo[0], meta);
      if (types != null) {
        mapping.setType(types);
      }
      if (sizes != null) {
        mapping.setSizes(sizes);
      }
      SQLParameter parameter = mapping.getUpdateParamter();
      if (whereClause == null) {
        if (mapping.isNullPK()) {
          return -1;
        }
        return this.session.executeUpdate(mapping.getUpdateSQL(), parameter);
      } 
      
      addParameter(parameter, param);
      if (mapping.isNullPK()) {
        return this.session.executeUpdate(mapping.getUpdateSQL() + " WHERE " + whereClause, parameter);
      }
      return this.session.executeUpdate(mapping.getUpdateSQL() + " AND " + whereClause, parameter);
    } 

    
    for (int i = 0; i < vo.length; i++) {
      if (vo[i] != null) {

        
        BeanMapping mapping = new BeanMapping(vo[i], meta);
        if (types != null) {
          mapping.setType(types);
        }
        SQLParameter parameter = mapping.getUpdateParamter();
        if (whereClause == null) {
          if (mapping.isNullPK()) {
            return -1;
          }
          this.session.addBatch(mapping.getUpdateSQL(), parameter);
        } else {
          
          addParameter(parameter, param);
          if (mapping.isNullPK()) {
            this.session.addBatch(mapping.getUpdateSQL() + " WHERE " + whereClause, parameter);
          } else {
            this.session.addBatch(mapping.getUpdateSQL() + " AND " + whereClause, parameter);
          } 
        } 
      } 
    }  return this.session.executeBatch();
  }
  
  private void addParameter(SQLParameter parameter, SQLParameter addParams) {
    if (addParams != null) {
      for (int i = 0; i < addParams.getCountParams(); i++) {
        parameter.addParam(addParams.get(i));
      }
    }
  }




  
  public int updateObject(Object[] vo, IMappingMeta meta) throws DbException { return updateObject(vo, meta, null); }

  
  public int delete(List vos) throws DbException {
    isNull(vos);
    return delete((SuperVO[])vos.toArray(new SuperVO[0]));
  }
  
  public int delete(SuperVO vo) throws DbException {
    isNull(vo);
    return delete(new SuperVO[] { vo });
  }
  
  public int delete(SuperVO[] vo) throws DbException {
    isNull(vo);
    if (vo.length == 0) {
      return 0;
    }
    
    String sql = SQLHelper.getDeleteByPKSQL(getTableName(vo[0]), vo[0].getPKFieldName());
    
    for (int i = 0; i < vo.length; i++) {
      if (vo[i] != null) {

        
        SQLParameter parameter = new SQLParameter();
        parameter.addParam(vo[i].getPrimaryKey());
        this.session.addBatch(sql, parameter);
      } 
    }  return this.session.executeBatch();
  }

  
  public void deleteObject(Object vo, IMappingMeta meta) throws DbException { deleteObject(new Object[] { vo }, meta); }

  
  public void deleteObject(Object[] vos, IMappingMeta meta) throws DbException {
    isNull(vos);
    if (vos.length == 0) {
      return;
    }
    for (int i = 0; i < vos.length; i++) {
      if (vos[i] != null) {

        
        BeanMapping mapping = new BeanMapping(vos[i], meta);
        SQLParameter parameter = mapping.getDeleteParamter();
        this.session.addBatch(mapping.getDeleteSQL(), parameter);
      } 
    }  this.session.executeBatch();
  }

  
  public void deleteObject(Object[] vos, IMappingMeta meta, boolean dealNull) throws DbException {
    isNull(vos);
    if (vos.length == 0) {
      return;
    }
    for (int i = 0; i < vos.length; i++) {
      if (vos[i] != null) {

        
        BeanMapping mapping = new BeanMapping(vos[i], meta, dealNull);
        SQLParameter parameter = mapping.getDeleteParamter();
        this.session.addBatch(mapping.getDeleteSQL(), parameter);
      } 
    }  this.session.executeBatch();
  }








  
  public int deleteByPK(IMappingMeta meta, String pk) throws DbException { return deleteByPKs(meta, new String[] { pk }); }










  
  public int deleteByPKs(IMappingMeta meta, String[] pks) throws DbException {
    String sql = "DELETE FROM " + meta.getTableName() + " WHERE " + meta.getPrimaryKey() + "=?";
    for (int i = 0; i < pks.length; i++) {
      SQLParameter parameter = new SQLParameter();
      parameter.addParam(pks[i]);
      this.session.addBatch(sql, parameter);
    } 
    return this.session.executeBatch();
  }




  
  public int deleteByPK(Class className, String pk) throws DbException { return deleteByPKs(className, new String[] { pk }); }





  
  public int deleteByPKs(Class className, String[] pks) throws DbException { return deleteByPKs(className, pks, null); }

  
  public int deleteByPKs(Class className, String[] pks, EXTableParam exParam) throws DbException {
    SuperVO supervo = initSuperVOClass(className);
    String[] splitTableNames = getCurSplitTable(supervo.getTableName(), exParam);
    if (splitTableNames != null && splitTableNames.length > 0) {
      int i = 0;
      for (String tableName : splitTableNames) {
        i += deleteTableByPK(supervo.getPKFieldName(), tableName, pks);
      }
      return i;
    }  if (EXTableMgrFactory.getInstance().isEXTable(supervo.getTableName())) {
      Map<String, List<String>> tables = DAOUtil.getSplitTableNames(supervo.getTableName(), pks);
      if (tables != null && tables.size() > 0) {
    	 int i = 0;
        for (String table : tables.keySet()) {
          List<String> curPKs = (List)tables.get(table);
          deleteTableByPK(supervo.getPKFieldName(), table, (String[])curPKs.toArray(new String[curPKs.size()]));
        } 
        return this.session.executeBatch();
      } 
    } 

    
    return deleteTableByPK(supervo.getPKFieldName(), supervo.getTableName(), pks);
  }
  
  private int deleteTableByPK(String pkColumnName, String tableName, String[] pks) throws DbException {
    String sql = "DELETE FROM " + tableName + " WHERE " + pkColumnName + "=?";
    for (String pk : pks) {
      SQLParameter parameter = new SQLParameter();
      parameter.addParam(pk);
      this.session.addBatch(sql, parameter);
    } 
    return this.session.executeBatch();
  }









  
  public int deleteByClause(IMappingMeta meta, String wherestr) throws DbException { return deleteByClause(meta, wherestr, null); }








  
  public int deleteByClause(Class className, String wherestr) throws DbException { return deleteByClause(className, wherestr, null); }



  
  public int deleteByClause(Class className, String wherestr, SQLParameter params) throws DbException { return deleteByClause(className, wherestr, params, null); }


  
  public int deleteByClause(Class<?> className, String wherestr, SQLParameter params, EXTableParam exParam) throws DbException {
    SuperVO supervo = initSuperVOClass(className);
    String[] splitTableNames = getCurSplitTable(supervo.getTableName(), exParam);
    String[] tableNames = null;
    if (splitTableNames == null || splitTableNames.length == 0) {
      tableNames = new String[] { supervo.getTableName() };
    } else {
      tableNames = splitTableNames;
    } 
    int i = 0;
    for (String tableName : tableNames) {
      String sql = "DELETE FROM " + tableName;
      if (wherestr != null) {
        wherestr = wherestr.trim();
        if (wherestr.length() > 0) {
          if (wherestr.toLowerCase().startsWith("WHERE")) {
            wherestr = wherestr.substring(5);
          }
          if (wherestr.length() > 0) {
            sql = sql + " WHERE " + wherestr;
          }
        } 
      } 
      if (params == null) {
        i += this.session.executeUpdate(sql);
      } else {
        i += this.session.executeUpdate(sql, params);
      } 
    } 
    return i;
  }

  
  public Collection retrieveByCorp(Class c, String pkCorp) throws DbException { return retrieveByCorp(c, pkCorp, null); }


  
  public Collection retrieveByCorp(Class c, String pkCorp, String[] selectedFields) throws DbException {
    if (pkCorp.equals("0001") || pkCorp.equals("@@@@")) {
      SQLParameter param = new SQLParameter();
      param.addParam("0001");
      param.addParam("@@@@");
      return retrieveByClause(c, "pk_corp=? or pk_corp=?", selectedFields, param);
    } 
    SQLParameter param = new SQLParameter();
    param.addParam(pkCorp);
    return retrieveByClause(c, "pk_corp=?", selectedFields, param);
  }








  
  public Collection retrieveByCorp(Class c, IMappingMeta meta, String pkCorp) throws DbException { return retrieveByCorp(c, meta, pkCorp, null); }








  
  public Collection retrieveByCorp(Class c, IMappingMeta meta, String pkCorp, String[] selectedFields) throws DbException {
    if (pkCorp.equals("0001") || pkCorp.equals("@@@@")) {
      SQLParameter param = new SQLParameter();
      param.addParam("0001");
      param.addParam("@@@@");
      return retrieveByClause(c, meta, "pk_corp=? or pk_corp=?", selectedFields, param);
    } 
    SQLParameter param = new SQLParameter();
    param.addParam(pkCorp);
    return retrieveByClause(c, meta, "pk_corp=?", selectedFields, param);
  }





  
  public Object retrieveByPK(Class className, String pk) throws DbException { return retrieveByPK(className, pk, null); }






  
  public Object retrieveByPK(Class className, String pk, String[] selectedFields) throws DbException { return retrieveByPK(className, pk, selectedFields, null); }



  
  public Object retrieveByPK(Class className, String pk, String[] selectedFields, EXTableParam exParam) throws DbException {
    SuperVO vo = initSuperVOClass(className);
    if (pk == null) {
      throw new IllegalArgumentException("pk is null");
    }
    SQLParameter param = new SQLParameter();
    param.addParam(pk.trim());
    BaseProcessor processor = new BeanListProcessor(className);
    String srcTable = vo.getTableName();
    String[] resultTables = null;
    if (EXTableMgrFactory.getInstance().isEXTable(srcTable)) {
      String splitTableName = "";
      String[] splitTableNames = getCurSplitTable(srcTable, exParam);
      if (splitTableNames != null && splitTableNames.length > 0) {
        splitTableName = splitTableNames[0];
      }
      if (StringUtil.isEmptyWithTrim(splitTableName)) {
        splitTableName = DAOUtil.getSplitTableName(srcTable, pk);
      }
      if (!StringUtil.isEmptyWithTrim(splitTableName)) {
        resultTables = new String[] { splitTableName };
      } else {
        resultTables = EXTableMgrFactory.getInstance().getSplitTableNames(srcTable);
      } 
    } else {
      resultTables = new String[] { srcTable };
    } 
    
    for (String tableName : resultTables) {
      if (!StringUtil.isEmptyWithTrim(tableName)) {
        tableName = UAPESAPI.sqlEncode(tableName);
        List<?> results = (List)this.session.executeQuery(
            buildSql(vo, vo.getPKFieldName() + "=?", selectedFields, tableName), param, processor);
        if (results.size() >= 1) {
          return results.get(0);
        }
      } 
    } 
    return null;
  }







  
  public Object retrieveByPK(Class className, IMappingMeta meta, String pk) throws DbException { return retrieveByPK(className, meta, pk, null); }








  
  public Object retrieveByPK(Class className, IMappingMeta meta, String pk, String[] selectedFields) throws DbException {
    if (pk == null) {
      throw new IllegalArgumentException("pk is null");
    }
    SQLParameter param = new SQLParameter();
    param.addParam(pk.trim());
    List results = (List)retrieveByClause(className, meta, meta.getPrimaryKey() + "=?", selectedFields, param);
    if (results.size() >= 1) {
      return results.get(0);
    }
    return null;
  }




  
  public Collection retrieve(SuperVO vo, boolean isAnd) throws DbException { return retrieve(vo, isAnd, null); }


  
  public Collection retrieve(Object vo, IMappingMeta meta) throws DbException {
    isNull(vo);
    BeanMapping mapping = new BeanMapping(vo, meta);
    
    String sql = mapping.getSelectwithParamSQL();
    SQLParameter param = mapping.getSelectParameter();
    
    return (Collection)this.session.executeQuery(sql, param, new BeanMappingListProcessor(vo.getClass(), meta));
  }

  
  public Collection retrieve(SuperVO vo, boolean isAnd, String[] fields) throws DbException { return (Collection)retrieve(vo, isAnd, fields, new BeanListProcessor(vo.getClass())); }

  
  public Collection retrieve(SuperVO vo, boolean isAnd, String[] fields, String[] orderbyFields) throws DbException {
    isNull(vo);
    String tableName = vo.getTableName();
    
    Map types = getColmnTypes(tableName);
    
    String[] names = getNotNullValidNames(vo, types);
    
    String sql = SQLHelper.getSelectSQL(tableName, names, isAnd, fields);
    
    sql = appendOrderBy(sql, orderbyFields);
    
    SQLParameter param = getSQLParam(vo, names);
    
    return (Collection)this.session.executeQuery(sql, param, new BeanListProcessor(vo.getClass()));
  }
  
  private String appendOrderBy(String sql, String[] orderBys) {
    if (sql == null) {
      throw new RuntimeException("sql is null");
    }
    
    if (orderBys == null || orderBys.length == 0) {
      return sql;
    }
    
    StringBuffer orderClause = new StringBuffer(" ORDER BY ");
    
    int len = orderClause.length();
    
    for (String s : orderBys) {
      if (s != null) {
        orderClause.append(s).append(',');
      }
    } 
    
    if (orderClause.length() > len) {
      orderClause.setLength(orderClause.length() - 1);
      
      return sql + orderClause;
    } 
    return sql;
  }



  
  public Object retrieve(SuperVO vo, boolean isAnd, String[] fields, ResultSetProcessor processor) throws DbException {
    isNull(vo);
    String tableName = vo.getTableName();
    
    Map types = getColmnTypes(tableName);
    
    String[] names = getNotNullValidNames(vo, types);
    
    String sql = SQLHelper.getSelectSQL(tableName, names, isAnd, fields);
    SQLParameter param = getSQLParam(vo, names);
    
    return this.session.executeQuery(sql, param, processor);
  }






  
  public Collection retrieveAll(Class className, IMappingMeta meta) throws DbException {
    Object vo = InitClass(className);
    BeanMapping mapping = new BeanMapping(vo, meta);
    
    return (Collection)this.session.executeQuery(mapping.getSelectSQL(), new BeanMappingListProcessor(className, meta));
  }





  
  public Collection retrieveAll(Class className) throws DbException {
    SuperVO vo = initSuperVOClass(className);
    String tableName = vo.getTableName();
    String sql = "SELECT * FROM " + tableName;
    
    return (Collection)this.session.executeQuery(sql, new BeanListProcessor(className));
  }





  
  public Collection retrieveByClause(Class className, String condition) throws DbException { return retrieveByClause(className, condition, null); }



  
  public Collection retrieveByClause(Class className, String condition, String[] fields, SQLParameter parameters) throws DbException { return retrieveByClause(className, condition, fields, parameters, null); }


  
  public Collection retrieveByClause(Class className, String condition, String[] fields, SQLParameter parameters, EXTableParam exParam) throws DbException {
    BaseProcessor processor = new BeanListProcessor(className);
    SuperVO vo = (SuperVO)InitClass(className);
    String[] splitTableNames = getCurSplitTable(vo.getTableName(), exParam);
    Collection col = null;
    for (String table : splitTableNames) {
      Collection curCol = (Collection)this.session.executeQuery(buildSql(vo, condition, fields, table), parameters, processor);
      
      if (col == null) {
        col = curCol;
      } else {
        col.addAll(curCol);
      } 
    } 
    return col;
  }

  
  private String buildSql(SuperVO vo, String condition, String[] fields, String curTableName) {
    String pkName = vo.getPKFieldName();
    boolean hasPKField = false;
    StringBuffer buffer = new StringBuffer();
    String tableName = StringUtil.isEmptyWithTrim(curTableName) ? vo.getTableName() : curTableName;
    if (fields == null) {
      buffer.append("SELECT * FROM ").append(tableName);
    } else {
      buffer.append("SELECT ");
      for (int i = 0; i < fields.length; i++) {
        if (fields[i] != null) {
          buffer.append(fields[i]).append(",");
          if (fields[i].equalsIgnoreCase(pkName)) {
            hasPKField = true;
          }
        } 
      } 
      if (!hasPKField) {
        buffer.append(pkName).append(",");
      }
      buffer.setLength(buffer.length() - 1);
      buffer.append(" FROM ").append(tableName);
    } 
    if (condition != null && condition.length() != 0) {
      if (condition.toUpperCase().trim().startsWith("ORDER ")) {
        buffer.append(" ").append(condition);
      } else {
        buffer.append(" WHERE ").append(condition);
      } 
    }
    
    return buffer.toString();
  }

  
  public Collection retrieveByClause(Class className, String condition, String[] fields) throws DbException { return retrieveByClause(className, condition, fields, null); }











  
  public Collection retrieveByClause(Class className, IMappingMeta meta, String condition, String[] fields) throws DbException { return retrieveByClause(className, meta, condition, fields, null); }


  
  public int getDBType() { return this.session.getDbType(); }





  
  public DatabaseMetaData getMetaData() {
    if (this.dbmd == null) {
      this.dbmd = getJdbcSession().getMetaData();
    }
    return this.dbmd;
  }




  
  public void setAddTimeStamp(boolean isAddTimeStamp) { this.session.setAddTimeStamp(isAddTimeStamp); }





  
  public void setSQLTranslator(boolean isTranslator) { this.session.setSQLTranslator(isTranslator); }

  
  public String getCatalog() {
    String catalog = null;
    switch (getDBType()) {
      case 7:
      case 8:
      case 14:
      case 15:
      case 16:
        try {
          catalog = getConnection().getCatalog();
        } catch (SQLException e) {
          Logger.error_null("", e);
        } 
        break;
      
      case 0:
      case 2:
      case 9:
        catalog = null;
        break;
      case 1:
        catalog = null;
        break;
      case 11:
        catalog = null;
        break;
      case 12:
        catalog = null;
        break;
      case 10:
        catalog = null;
        break;
      case 13:
        catalog = null;
        break;
      
      case 6:
        catalog = "";
        break;
    } 

    
    return catalog;
  }
  
  public String getSchema() {
    String strSche = null;
    try {
      String schema = getMetaData().getUserName();
      switch (getDBType()) {
        case 7:
        case 9:
        case 14:
        case 15:
        case 16:
          strSche = null;
          break;
        case 2:
          strSche = "dbo";
          break;
        case 0:
        case 1:
        case 6:
        case 10:
        case 11:
        case 12:
        case 13:
          if (schema == null || schema.length() == 0) {
            throw new IllegalArgumentException("ORACLE Database mode does not allow to be null!!");
          }
          
          strSche = schema.toUpperCase();
          break;
      } 


    
    } catch (Exception e) {
      Logger.error(e.getMessage(), e);
    } 
    return strSche;
  }

  
  public void setReadOnly(boolean isReadOnly) throws DbException { this.session.setReadOnly(isReadOnly); }






  
  private void init() throws DbException {
    if (this.dataSource == null) {
      this.session = new JdbcSession();
    } else {
      this.session = new JdbcSession(this.dataSource);
    } 
    
    this.session.setMaxRows(this.maxRows);
  }
  
  private void isNull(Object vo) {
    if (vo == null) {
      throw new IllegalArgumentException("vo object parameter is null!!");
    }
  }
  
  public void setMaxRows(int maxRows) {
    super.setMaxRows(maxRows);
    this.session.setMaxRows(maxRows);
  }








  
  public Collection retrieveByClause(Class className, IMappingMeta meta, String condition) throws DbException { return retrieveByClause(className, meta, condition, meta.getColumns()); }










  
  private SQLParameter getSQLParam(SuperVO vo, String[] names, Map<String, Integer> types, Map<String, Integer> size) {
    SQLParameter params = new SQLParameter();
    for (int i = 0; i < names.length; i++) {
      if (names[i].equalsIgnoreCase("ts")) {
        continue;
      }
      int type = ((Integer)types.get(names[i].toUpperCase())).intValue();
      Object value = vo.getAttributeValue(names[i]);
      if (value == null && type == 12) {
        Integer length = (Integer)size.get(names[i].toUpperCase());
        if (length != null && (length.intValue() == 20 || length.intValue() == 36 || length.intValue() == 101)) {
          params.addParam("~");
          continue;
        } 
      } 
      if (value == null && type == -9) {
        Integer length = (Integer)size.get(names[i].toUpperCase());
        if (length != null && (length.intValue() == 20 || length.intValue() == 36 || length.intValue() == 101)) {
          params.addParam("~");
          continue;
        } 
      } 
      if (value == null) {
        params.addNullParam(type);
      
      }
      else if (type == 2004 || type == -4 || type == -3 || type == -2) {
        params.addBlobParam(value);
      
      }
      else if (type == 2005 || type == -1) {
        params.addClobParam(String.valueOf(value));
      } else {
        
        params.addParam(value);
      }  continue;
    } 
    return params;
  }







  
  private String[] getValidNames(SuperVO vo, Map types) {
    String[] names = vo.getAttributeNames();
    List nameList = new ArrayList();
    for (int i = 0; i < names.length; i++) {
      if (types.get(names[i].toUpperCase()) != null && !names[i].equalsIgnoreCase("ts")) {
        nameList.add(names[i]);
      }
    } 
    return (String[])nameList.toArray(new String[0]);
  }








  
  private String[] getUpdateValidNames(SuperVO vo, Map types, String pkName) {
    String[] names = vo.getAttributeNames();
    List nameList = new ArrayList();
    for (int i = 0; i < names.length; i++) {
      if (types.get(names[i].toUpperCase()) != null && !names[i].equalsIgnoreCase(pkName) && 
        !names[i].equalsIgnoreCase("ts")) {
        nameList.add(names[i]);
      }
    } 
    return (String[])nameList.toArray(new String[0]);
  }





  
  private String[] getNotNullValidNames(SuperVO vo, Map type) {
    String[] names = vo.getAttributeNames();
    List nameList = new ArrayList();
    for (int i = 0; i < names.length; i++) {
      if (type.get(names[i].toUpperCase()) != null && vo.getAttributeValue(names[i]) != null) {
        nameList.add(names[i]);
      }
    } 
    if (nameList.size() == 0) {
      return new String[0];
    }
    return (String[])nameList.toArray(new String[0]);
  }





  
  private SQLParameter getSQLParam(SuperVO vo, String[] names) {
    if (names == null || names.length == 0) {
      return null;
    }
    SQLParameter parameter = new SQLParameter();
    for (int i = 0; i < names.length; i++) {
      parameter.addParam(vo.getAttributeValue(names[i]));
    }
    return parameter;
  }
  
  public Connection getConnection() {
    if (this.session != null) {
      return this.session.getConnection();
    }
    return null;
  }
  
  private ColCache getColCache() {
    String ds = ds();
    synchronized (colCacheMap) {
      ColCache colCache = (ColCache)colCacheMap.get(ds);
      if (colCache == null) {
        colCache = new ColCache();
        colCacheMap.put(ds, colCache);
      } 
      return colCache;
    } 
  }

  
  private String ds() { return (this.dataSource == null) ? DataSourceCenter.getInstance().getSourceName() : this.dataSource; }

  
  private Map<String, Integer> getColmnSize(String table) throws DbException {
    table = UAPESAPI.sqlEncode(table);
    ColCache cache = getColCache();
    
    Map<String, Integer> result = (Map)cache.sizeCache.get(table);
    if (result == null || result.size() == 0) {
      result = new HashMap<String, Integer>();
      ResultSet rsColumns = null;
      try {
        if (getDBType() == 2 && table.startsWith("#")) {
        	Statement stmt = null;
          try {
            stmt = getConnection().createStatement();
            rsColumns = stmt.executeQuery("select top 0 * from " + table);
            ResultSetMetaData rsMeta = rsColumns.getMetaData();
            int count = rsMeta.getColumnCount();
            
            for (int i = 1; i < count + 1; i++) {
              result.put(rsMeta.getColumnName(i).toUpperCase(), Integer.valueOf(rsMeta.getPrecision(i)));
            }
            if (result.size() >= 0) {
              cache.sizeCache.put(table, result);
            } else {
              throw new UnKnownException("no column info for: " + table + " at datasource: " + ds());
            } 

            
            return result;
          } finally {
            if (stmt != null) {
              stmt.close();
            }
          } 
        } 
        DatabaseMetaData dmd = getConnection().getMetaData();
        if (getDBType() == 1 || getDBType() == 6 || getDBType() == 0 || 
          getDBType() == 10 || getDBType() == 11 || 
          getDBType() == 13 || getDBType() == 12) {
          rsColumns = dmd.getColumns(null, dmd.getUserName().toUpperCase(), table.toUpperCase(), "%");
        } else if (getDBType() == 7 || getDBType() == 15 || 
          getDBType() == 14 || getDBType() == 16) {
          rsColumns = getMetaData().getColumns(null, null, table.toLowerCase(), "%");
        } else if (getDBType() == 9) {
          rsColumns = getMetaData().getColumns(null, null, table.toLowerCase(), "%");
        } else if (getDBType() == 8) {
          rsColumns = dmd.getColumns(getCatalog(), dmd.getUserName(), table, "%");
        } else {
          rsColumns = dmd.getColumns(null, null, table.toUpperCase(), "%");
        } 
        while (rsColumns.next()) {
          result.put(rsColumns.getString("COLUMN_NAME").toUpperCase(), Integer.valueOf(rsColumns.getInt("COLUMN_SIZE")));
        }
        
        if (result.size() >= 0) {
          cache.sizeCache.put(table, result);
        } else {
          throw new UnKnownException("no column info for: " + table + " at datasource: " + ds());
        }
      
      }
      catch (SQLException e) {
        Logger.error("get table metadata error", e);
        throw ExceptionFactory.getException(getDBType(), "get table metadata error", e);
      } finally {
        DBUtil.closeRs(rsColumns);
      } 
    } 
    return result;
  }







  
  private Map<String, Integer> getColmnTypes(String table) throws DbException {
    table = UAPESAPI.sqlEncode(table);
    
    ColCache cache = getColCache();
    
    Map<String, Integer> result = (Map)cache.typeCache.get(table);
    
    Map<String, Integer> sizeMap = (Map)cache.sizeCache.get(table);
    if (result == null || result.size() == 0) {
      boolean querySize = false;
      if (sizeMap == null) {
        sizeMap = new HashMap<String, Integer>();
        querySize = true;
      } 
      Map<String, Integer> typeMap = new HashMap<String, Integer>();
      ResultSet rsColumns = null;
      try {
        if (getDBType() == 2 && table.startsWith("#")) {
        	Statement stmt = null;
          try {
            stmt = getConnection().createStatement();
            rsColumns = stmt.executeQuery("select top 0 * from " + table);
            ResultSetMetaData rsMeta = rsColumns.getMetaData();
            int count = rsMeta.getColumnCount();
            
            for (int i = 1; i < count + 1; i++) {
              typeMap.put(rsMeta.getColumnName(i), Integer.valueOf(rsMeta.getColumnType(i)));
              if (querySize) {
                sizeMap.put(rsMeta.getColumnName(i).toUpperCase(), Integer.valueOf(rsMeta.getPrecision(i)));
              }
            } 
            if (typeMap.size() > 0) {
              cache.typeCache.put(table, typeMap);
              cache.sizeCache.put(table, sizeMap);
            } else {
              throw new UnKnownException("no column info for: " + table + " at datasource: " + ds());
            } 

            
            return typeMap;
          } finally {
            if (stmt != null) {
              stmt.close();
            }
          } 
        } 

        
        if (getDBType() == 2) {
          rsColumns = getMetaData().getColumns(null, null, table.toUpperCase(), "%");
        } else if (getDBType() == 7 || getDBType() == 16) {
          rsColumns = getMetaData().getColumns(null, null, table.toLowerCase(), "%");
        } else if (getDBType() == 15) {
          rsColumns = getMetaData().getColumns(null, null, table.toLowerCase(), "%");
        } else if (getDBType() == 9 || getDBType() == 13) {
          rsColumns = getMetaData().getColumns(null, null, table.toLowerCase(), "%");
        } else if (getDBType() == 12) {
          rsColumns = getMetaData().getColumns(null, null, table.toLowerCase(), "%");
        } else {
          rsColumns = getMetaData().getColumns(null, getSchema(), table.toUpperCase(), "%");
        } 
        while (rsColumns.next()) {
          String columnName = rsColumns.getString("COLUMN_NAME").toUpperCase();
          int columnType = rsColumns.getShort("DATA_TYPE");
          typeMap.put(columnName, Integer.valueOf(columnType));
          if (querySize) {
            sizeMap.put(rsColumns.getString("COLUMN_NAME").toUpperCase(), 
                Integer.valueOf(rsColumns.getInt("COLUMN_SIZE")));
          }
        } 
        
        if (typeMap.size() > 0) {
          cache.typeCache.put(table, typeMap);
          cache.sizeCache.put(table, sizeMap);
        } else {
          throw new UnKnownException("no column info for: " + table + " at datasource: " + ds());
        } 

        
        return typeMap;
      }
      catch (SQLException e) {
        Logger.error("get table metadata error", e);
        throw ExceptionFactory.getException(getDBType(), "get table metadata error", e);
      } finally {
        DBUtil.closeRs(rsColumns);
      } 
    } 
    return result;
  }
  
  public static void clearAllTableInfo() throws DbException {
    if (colCacheMap.size() == 0) {
      return;
    }
    for (ColCache colCache : colCacheMap.values()) {
      colCache.typeCache.clear();
      colCache.sizeCache.clear();
    } 
  }
  
  public static void clearColumnTypes(String table) throws DbException {
    if (colCacheMap.size() == 0) {
      return;
    }
    for (ColCache colCache : colCacheMap.values()) {
      colCache.typeCache.remove(table);
      colCache.sizeCache.remove(table);
    } 
  }
  
  private Object InitClass(Class className) {
    try {
      return className.newInstance();
    } catch (InstantiationException e) {
      throw new IllegalArgumentException("Parameter Class can not be instantiated!!");
    } catch (IllegalAccessException e) {
      throw new IllegalArgumentException("Illegal Parameter!!");
    } 
  }
  
  private SuperVO initSuperVOClass(Class className) {
    Object vo;
    try {
      vo = className.newInstance();
    } catch (InstantiationException e) {
      throw new IllegalArgumentException("Parameter Class can not be instantiated!!");
    } catch (IllegalAccessException e) {
      throw new IllegalArgumentException(" Illegal Parameter!!");
    } 
    if (!(vo instanceof SuperVO)) {
      throw new IllegalArgumentException("Parameter Class is not SuperVO");
    }
    return (SuperVO)vo;
  }





  
  private String[] getCurSplitTable(String srcTable, EXTableParam exParam) {
    if (EXTableMgrFactory.getInstance().isEXTable(srcTable)) {
      exParam = DAOUtil.getCurExParam(srcTable, exParam);
      if (exParam != null) {
        return EXTableInnerTool.getSplitTableName(srcTable, exParam.getExValues());
      }
      
      return new String[0];
    } 
    
    return new String[] { srcTable };
  }



  
  public Collection retrieveByClause(Class className, IMappingMeta meta, String condition, String[] fields, SQLParameter params) throws DbException {
    String sql = SQLHelper.getSelectSQL(meta.getTableName(), fields);
    if (condition != null && condition.length() != 0) {
      if (condition.trim().toUpperCase().startsWith("ORDER ")) {
        sql = sql + " " + condition;
      } else {
        sql = sql + " WHERE " + condition;
      } 
    }
    BaseProcessor processor = new BeanMappingListProcessor(className, meta, fields);
    if (params != null) {
      return (Collection)this.session.executeQuery(sql, params, processor);
    }
    return (Collection)this.session.executeQuery(sql, processor);
  }


  
  public int deleteByClause(IMappingMeta meta, String wherestr, SQLParameter params) throws DbException {
    String sql = "DELETE FROM " + meta.getTableName();
    if (wherestr != null) {
      wherestr = wherestr.trim();
      if (wherestr.length() > 0) {
        if (wherestr.toLowerCase().startsWith("WHERE")) {
          wherestr = wherestr.substring(5);
        }
        if (wherestr.length() > 0) {
          sql = sql + " WHERE " + wherestr;
        }
      } 
    } 
    if (params == null) {
      return this.session.executeUpdate(sql);
    }
    return this.session.executeUpdate(sql, params);
  }





  
  private String[] insert(SuperVO[] vos, IMappingMeta meta, boolean withPK) throws DbException {
    String[] pks = null;
    String tableName = meta.getTableName();
    
    Map<String, Integer> types = getColmnTypes(tableName);
    Map<String, Integer> sizes = getColmnSize(tableName);
    
    String sql = SQLHelper.getInsertSQL(tableName, meta.getColumns());
    
    pks = preparePK(vos, withPK);
    
    if (vos.length == 1) {
      SQLParameter parameter = getSQLParam(vos[0], meta.getAttributes(), meta.getColumns(), types, sizes);
      this.session.executeUpdate(sql, parameter);
    } else {
      SQLParameter[] parameters = new SQLParameter[vos.length];
      for (int i = 0; i < vos.length; i++) {
        if (vos[i] != null)
        {
          
          parameters[i] = getSQLParam(vos[i], meta.getAttributes(), meta.getColumns(), types, sizes); } 
      } 
      this.session.addBatch(sql, parameters);
      this.session.executeBatch();
    } 
    
    return pks;
  }





  
  private int update(SuperVO[] vo, IMappingMeta meta, String whereClause, SQLParameter param) throws DbException {
    isNull(vo);
    if (vo.length == 0) {
      return 0;
    }
    int row = 0;
    String tableName = meta.getTableName();
    String pkName = meta.getPrimaryKey();
    Map<String, Integer> types = getColmnTypes(tableName);
    Map<String, Integer> sizes = getColmnSize(tableName);
    String sql = SQLHelper.getUpdateSQL(tableName, meta.getColumns(), pkName);
    if (vo.length == 1) {
      SQLParameter parameter = getSQLParam(vo[0], meta.getAttributes(), meta.getColumns(), types, sizes);
      parameter.addParam(vo[0].getAttributeValue(pkName));
      if (whereClause == null) {
        row = this.session.executeUpdate(sql, parameter);
      } else {
        addParameter(parameter, param);
        row = this.session.executeUpdate(sql + " and " + whereClause, parameter);
      } 
    } else {
      for (int i = 0; i < vo.length; i++) {
        if (vo[i] != null) {

          
          SQLParameter parameter = getSQLParam(vo[i], meta.getAttributes(), meta.getColumns(), types, sizes);
          parameter.addParam(vo[i].getAttributeValue(pkName));
          if (whereClause == null) {
            this.session.addBatch(sql, parameter);
          } else {
            addParameter(parameter, param);
            this.session.addBatch(sql + " and " + whereClause, parameter);
          } 
        } 
      }  row = this.session.executeBatch();
    } 
    return row;
  }




  
  private SQLParameter getSQLParam(SuperVO vo, String[] attribNames, String[] columnNames, Map<String, Integer> types, Map<String, Integer> size) {
    SQLParameter params = new SQLParameter();
    for (int i = 0; i < attribNames.length; i++) {
      if (attribNames[i].equalsIgnoreCase("ts")) {
        continue;
      }
      
      int type = -1;
      try {
    	
    	  String columns = columnNames[i].toUpperCase();
    	  if("PK_JOBGRADE".equals(columns) || "PK_JOBRANK".equals(columns) || "VDEF1".equals(columns)) {
    		  type = 3001;
    	  }else {
    		  type = ((Integer)types.get(columnNames[i].toUpperCase())).intValue();
    	  }
        
      } catch (NullPointerException e) {
        Logger.error("get param type error, types=" + types + ",columnNames=" + columnNames[i] + ",i=" + i);
        throw e;
      } 
      Object value = vo.getAttributeValue(attribNames[i]);
      if (value == null && type == 12) {
        int length = ((Integer)size.get(columnNames[i].toUpperCase())).intValue();
        if (length == 20 || length == 36 || length == 101) {
          params.addParam("~");
          continue;
        } 
      } 
      if (value == null && type == -9) {
        int length = ((Integer)size.get(columnNames[i].toUpperCase())).intValue();
        if (length == 20 || length == 36 || length == 101) {
          params.addParam("~");
          continue;
        } 
      } 
      if (type == 3001) {
    	  if (value == null) {
    		  params.addParam("~");
    	      }else {
    	    	  params.addParam(value);
    	      }
        }
      else if (value == null && type != 3001) {
        params.addNullParam(type);
      
      }
      else if (type == 2004 || type == -4 || type == -3 || type == -2) {
        params.addBlobParam(value);
      
      }
      else if (type == 2005 || type == -1) {
        params.addClobParam(String.valueOf(value));
      } else {
        
        params.addParam(value);
      }  continue;
    } 
    return params;
  }
}